﻿CREATE procedure  [dbo].[_t1]
AS
SELECT     RolesID AS cID, RolesN AS hName, null AS mgrid, 0 AS hrclvl, '.' + convert(varchar,RolesID) + '.' AS hrccrt, null as UserID
FROM        stRoles
UNION ALL
SELECT     c.URolesID AS cID, b.UserN AS hName, c.RolesID AS mgrid, 1 AS hrclvl, 
                  '.' + convert(varchar,c.RolesID) + '.' + convert(varchar,b.UserID) + '.' AS hrccrt, b.UserID
FROM        stURoles AS c INNER JOIN
                  stRoles AS a ON c.RolesID = a.RolesID INNER JOIN
                  stUsers AS b ON c.UserID = b.UserID


